Define link and name of hyperlink using Excel VBA

HCL Notes/Domino 8.5 Forum (includes Notes Traveler)

Subject: Define link and name of hyperlink using Excel VBA

Feedback Type: Problem

Product Area: Notes Client

Technical Area: Functionality

Platform: Windows

Release: 8.5.1

Reproducible: Always

I have the code below which works great for sending my mail via lotus notes. I would just like to have the ssPath which appears in the body of the message appear as a hyperlink so that the user can click on the link and access the file.

When playing around with Lotus I found that the Create Hyperlink is easy to use when generatig a new email. If possible I would like to be able to define the 'link' text and the 'name' text for the hyperlink using my VBA code.

How do I modify my code to accomplish this?

Thanks

---------------
Private Sub CommandButton2_Click()

UserForm8.Hide
Application.ScreenUpdating = False
Application.EnableEvents = False

'In order to add attachment to e-mails in Lotus Notes.
Const EMBED_ATTACHMENT As Long = 1454

'Path to the folder that will store the temporarily workbook.
Dim stPath As String

'Name of the subject for the outgoing e-mail.
Const stSubject As String = "PO Review Request"

'Variable for the list of SendTo recipients.
Dim vaSendTo As Variant

' Variable for the complete pathway and the name of the temporarily workbook.
Dim stAttachment As String

'Variable for the complete message including the URL and e-mail address.
Dim stMsg As String

'Variables for Lotus Notes.
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object

stPath = ThisWorkbook.path & "\" & ThisWorkbook.Name

' Create the message.
If Range("AH66").Value = "SS" Then
stMsg = Range("AK66").Value & vbNewLine & vbNewLine & stPath & vbNewLine & vbNewLine & _
"Thank you," & vbNewLine & vbNewLine & Range("AK70").Value

ElseIf Range("AH66").Value = "LD" Then
stMsg = Range("AK67").Value & vbNewLine & vbNewLine & stPath & vbNewLine & vbNewLine & _
"Thank you," & vbNewLine & vbNewLine & Range("AK70").Value

ElseIf Range("AH66").Value = "PS" Then
stMsg = Range("AK68").Value & vbNewLine & vbNewLine & stPath & vbNewLine & vbNewLine & _
"Thank you," & vbNewLine & vbNewLine & Range("AK70").Value
End If

' Retrieve the SendTo recipients from a fix range.
vaSendTo = Range("AI62").Value

' Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")

' If Lotus Notes is not open then open the mail part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

' Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set noAttachment = noDocument.CreateRichTextItem("stAttachment")

'Add values to the created e-mail's main properties.
With noDocument
.Form = "Memo"
.SendTo = vaSendTo
.CopyTo = vaCopyTo
.Subject = stSubject
.Body = stMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0, vaSendTo
End With

'Release objects from memory.
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

MsgBox "The e-mail has successfully been created and distributed", vbInformation

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Feedback number WEBB83LSJW created by ~Lex Loptooskigon on 03/16/2010

Status: Open
Comments: